package cn.saltybe.analysis.service;

import cn.saltybe.analysis.entity.result.NewHouseAveragePrice;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;

@Service
public class NewHouseAveragePriceStatisticServiceImpl implements NewHouseAveragePriceStatisticService {
    private JdbcTemplate jdbcTemplate;

    // set方法注入JdbcTemplate
    @Autowired
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) throws Exception {
        this.jdbcTemplate = jdbcTemplate;
        this.createTable();
    }

    private final BeanPropertyRowMapper<NewHouseAveragePrice> rowMapper = new BeanPropertyRowMapper<>(NewHouseAveragePrice.class);

    public void createTable() throws Exception {
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        final String sql = "CREATE TABLE IF NOT EXISTS `new_house_average_price` (" +
                "`statistic_time` DATETIME NOT NULL," +
                "`city` VARCHAR(8) NOT NULL," +
                "`district` VARCHAR(8) NOT NULL," +
                "`average_price` DOUBLE NOT NULL" +
                ") ENGINE = InnoDB CHARSET = utf8mb4";
        this.jdbcTemplate.execute(sql);
    }

    public void dropTable() throws Exception {
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        final String sql = "DROP TABLE `new_house_average_price`";
        this.jdbcTemplate.execute(sql);
    }

    @Override
    public void insert(NewHouseAveragePrice newHouseAveragePrice) throws Exception {
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        final String sql = "INSERT INTO `new_house_average_price` (`statistic_time`, `city`, `district`, `average_price`) VALUES (?, ?, ?, ?)";
        Object[] params = new Object[] { newHouseAveragePrice.getStatistic_time(), newHouseAveragePrice.getCity(), newHouseAveragePrice.getDistrict(), newHouseAveragePrice.getAverage_price() };
        jdbcTemplate.update(sql, params);
    }

    @Override
    public List<NewHouseAveragePrice> all() throws Exception {
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        final String sql = "SELECT * FROM `new_house_average_price`";
        List<NewHouseAveragePrice> list = this.jdbcTemplate.query(sql, rowMapper);
        return list;
    }

    @Override
    public List<NewHouseAveragePrice> query(String city) throws Exception {
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        final String sql = "SELECT * FROM `new_house_average_price` WHERE `city` = ?";
        Object[] params = new Object[] { city };
        List<NewHouseAveragePrice> list = this.jdbcTemplate.query(sql, rowMapper, params);
        return list;
    }

    @Override
    public List<NewHouseAveragePrice> query(String[] city) throws Exception {
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        if (city.length == 0) return new ArrayList<NewHouseAveragePrice>();
        String conditions = "";
        Object[] params = new Object[city.length];
        for (int i = 0; i < city.length; i++) {
            conditions += "`city` = ?";
            params[i] = city[i];
            if (i + 1 != city.length) conditions += " OR ";
        }
        final String sql = "SELECT * FROM `new_house_average_price` WHERE " + conditions;
        List<NewHouseAveragePrice> list = this.jdbcTemplate.query(sql, rowMapper, params);
        return list;
    }

    @Override
    public int delete(String city, String district) throws Exception {
        if (this.jdbcTemplate == null) throw new Exception("ERR_INIT_DRIVER_FAILED");
        final String sql = "DELETE FROM `new_house_average_price` WHERE `city` = ? AND `district` = ?";
        Object[] params = new Object[] { city, district };
        return this.jdbcTemplate.update(sql, params);
    }
}
